import logging
from typing import Optional

from fastapi import APIRouter, Depends, Query
from pydantic import Field, BaseModel
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session

from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme, EncryptionMiddleware

data_good_info_router = APIRouter(prefix="/material/goodInfo", tags=["危化品管理"],
                                  dependencies=[Depends(oauth2_scheme)])


class GoodSearch(PaginationBase):
    fsiteNo: str = Field(None, description="场地编号")
    fcasno: str = Field(None, description="物料编号")
    fclassstno: str = Field(None, description="类型编号")
    fstorageflag: str = Field(None, description="")


@data_good_info_router.get("/list", summary="查询物料信息")
def list_good_info(good: GoodSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from lims.i_ts_good_info where 1=1"
    if good.fsiteNo is not None:
        sql = sql + " and fsite_no = :site_no "
        param["site_no"] = good.fsiteNo
    if good.fcasno is not None:
        sql = sql + " and ( (fgoodname like concat('%', :fcasno ,'%') or (fgoodno like  concat('%', :fcasno ,'%') )"
        param["fcasno"] = good.fcasno
    if good.fclassstno is not None:
        sql = sql + " and fclassstno = :fclassstno "
        param["fclassstno"] = good.fclassstno
    if good.fstorageflag is not None:
        sql = sql + " and fstorageflag = :fstorageflag "
        param["fstorageflag"] = good.fstorageflag
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select fsite_no,fgoodno,fgoodname,fcasno,fbarcode,fengname,fclassstno,fspec,funit,"
    sql = sql + " feffday,fpurity,fbuyprice,fstorageflag,fsafeqty,fheightqty,fsafecard,fordunit, "
    sql = sql + " fordpack,fsupno,fgoodstate,fupddate,foperator,fdanger,fdensity"
    sql = sql + " from lims.i_ts_good_info where 1=1"
    if good.fsiteNo is not None:
        sql = sql + " and fsite_no = :site_no "
        param["site_no"] = good.fsiteNo
    if good.fcasno is not None:
        sql = sql + " and ( (fgoodname like concat('%', :fcasno ,'%') or (fgoodno like  concat('%', :fcasno ,'%') )"
        param["fcasno"] = good.fcasno
    if good.fclassstno is not None:
        sql = sql + " and fclassstno = :fclassstno "
        param["fclassstno"] = good.fclassstno
    if good.fstorageflag is not None:
        sql = sql + " and fstorageflag = :fstorageflag "
        param["fstorageflag"] = good.fstorageflag
    if good.order == "desc":
        sql = sql + " order by  fclassstno,fgoodno desc "
    else:
        sql = sql + " order by  fclassstno,fgoodno  "
    sql = sql + " limit :limit offset :offset "
    param["limit"] = good.limit
    param["offset"] = good.offset
    data = db.execute(text(sql), param).fetchall()

    # details 明细列表
    param = {}
    sql_3 = " select b.fsite_no,b.fgoodno,b.frfidbarcode,b.fbarcode,b.fbegdate,b.fendflag,b.fenddate"
    sql_3 = sql_3 + " from lims.i_ts_good_info a,lims.i_ts_good_rfid b  "
    sql_3 = sql_3 + " where a.fsite_no=b.fsite_no"
    sql_3 = sql_3 + " and a.fgoodno=b.fgoodno"
    if good.fsiteNo is not None:
        sql_3 = sql_3 + " and a.fsite_no = :site_no "
        param["site_no"] = good.fsiteNo
    if good.fcasno is not None:
        sql_3 = sql_3 + " and a.fgoodname like concat('%', :fcasno ,'%') "
        param["fcasno"] = good.fcasno
    if good.fclassstno is not None:
        sql_3 = sql_3 + " and a.fclassstno = :fclassstno "
        param["fclassstno"] = good.fclassstno
    if good.fstorageflag is not None:
        sql_3 = sql_3 + " and a.fstorageflag = :fstorageflag "
        param["fstorageflag"] = good.fstorageflag
    sql_3 = sql_3 + " order by b.fsite_no,b.fgoodno,b.frfidbarcode"
    data_detail = db.execute(text(sql_3), param).fetchall()

    data_list = []
    for key in range(len(data)):
        fupddate = ""
        try:
            fupddate = data[key][20].strftime('%Y-%m-%dT%H:%M:%S')
        except Exception as e:
            print(e)

        if (fupddate == ""):
            fupddate = None

        d1 = {}
        d1['fsiteno'] = data[key][0]
        d1['fgoodno'] = data[key][1]
        d1['fgoodname'] = data[key][2]
        d1['fcasno'] = data[key][3]
        d1['fbarcode'] = data[key][4]
        d1['fengname'] = data[key][5]
        d1['fclassstno'] = data[key][6]
        d1['fspec'] = data[key][7]
        d1['funit'] = data[key][8]
        d1['feffday'] = data[key][9]
        d1['fpurity'] = data[key][10]
        d1['fbuyprice'] = str(data[key][11])
        d1['fstorageflag'] = data[key][12]
        d1['fsafeqty'] = data[key][13]
        d1['fheightqty'] = data[key][14]
        d1['fsafecard'] = data[key][15]
        d1['fordunit'] = data[key][16]
        d1['fordpack'] = data[key][17]
        d1['fsupno'] = data[key][18]
        d1['fgoodstate'] = data[key][19]
        d1['fupddate'] = fupddate
        d1['foperator'] = data[key][21]
        d1['fdanger'] = data[key][22]
        d1['fdensity'] = data[key][23]
        sbillno = data[key][1]

        detail_list4 = []
        for j1 in range(len(data_detail)):
            if (str(data_detail[j1][1]) == str(sbillno)):
                d2 = {}
                d2['fsiteNo'] = data_detail[j1][0]
                d2['fgoodno'] = data_detail[j1][1]
                d2['frfidbarcode'] = data_detail[j1][2]
                d2['fbarcode'] = data_detail[j1][3]
                d2['fbegdate'] = data_detail[j1][4]
                d2['fendflag'] = data_detail[j1][5]
                d2['fenddate'] = data_detail[j1][6]
                detail_list4.append(d2)

        d1["details4"] = detail_list4
        details = []
        d1["details"] = details
        details2 = []
        d1["details2"] = details2
        data_list.append(d1)

    info_dict = {"list": data_list,
                 "limit": good.limit,
                 "page": good.pageIndex,
                 "pages": (count + good.limit - 1) // good.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)


class HazardousMaterialCreate(BaseModel):
    """危化品物料创建模型"""
    fsiteNo: str = Field(..., description="站点编号")
    fgoodno: str = Field(..., description="物料编号")
    fgoodname: str = Field(..., description="物料名称")
    fclassstno: str = Field(..., description="分类编号")
    fstorageflag: str = Field(..., description="存储标志")

    # 可选字段（有默认值）
    fbarcode: str = Field("", description="条码")
    fcasno: str = Field("", description="CAS编号")
    fengname: str = Field("", description="英文名称")
    fspec: str = Field("", description="规格")
    funit: str = Field("克", description="单位，默认'克'")
    fordunit: str = Field("1", description="订货单位，默认'1'")
    fsafecard: str = Field("", description="安全卡")

    # 数值类型字段（默认值为字符串）
    fbuyprice: str = Field("0", description="购买价格，默认'0'")
    fdanger: str = Field("0", description="危险等级，默认'0'")
    fdensity: str = Field("1", description="密度，默认'1'")
    feffday: str = Field("0", description="有效期天数，默认'0'")
    fgoodstate: str = Field("1", description="物料状态，默认'1'")
    fheightqty: str = Field("1", description="最高数量，默认'1'")
    fordpack: str = Field("1", description="订货包装，默认'1'")
    fpurity: str = Field("0", description="纯度，默认'0'")
    fsafeqty: str = Field("0", description="安全数量，默认'0'")

    # 自动填充字段
    foperator: Optional[int] = Field(None, description="操作人ID")


@data_good_info_router.post("/create", summary="新增危化品耗材物料信息")
def create_good_info(good: HazardousMaterialCreate, db: Session = Depends(get_db), token: str = Depends(oauth2_scheme)):
    try:
        user_id = EncryptionMiddleware.get_current_user(token)
        with db.begin():
            sql = "select fgoodno from i_ts_good_info where fgoodno= :fgoodno  and fsite_No= :fsiteNo "
            data = db.execute(text(sql), {
                "fgoodno": good.fgoodno,
                "fsiteNo": good.fsiteNo,
            }).fetchall()

            if len(data) > 0:
                return BaseResponse.error(message="耗材编号已存在")
            else:
                sql = (
                    "insert into i_ts_good_info(fsite_no,fgoodno,fgoodname,fcasno,fbarcode,fengname,fclassstno,fspec,"
                    "funit,feffday,fpurity,fbuyprice,fstorageflag,fsafeqty,fheightqty,fsafecard,fordunit,fordpack,fgoodstate,fupddate,foperator)")
                sql = sql + (
                    " values( :fsiteNo , :fgoodno , :fgoodname , :fcasno , :fbarcode , :fengname , :fclassstno , :fspec , :funit , :feffday ,"
                    " :fpurity , :fbuyprice , :fstorageflag , :fsafeqty , :fheightqty , :fsafecard , :fordunit , :fordpack , :fgoodstate ,now(), :userid );")
                db.execute(text(sql), {
                    "fsiteNo": good.fsiteNo,
                    "fgoodno": good.fgoodno,
                    "fgoodname": good.fgoodname,
                    "fcasno": good.fcasno,
                    "fbarcode": good.fbarcode,
                    "fengname": good.fengname,
                    "fclassstno": good.fclassstno,
                    "fspec": good.fspec,
                    "funit": good.funit,
                    "feffday": good.feffday,
                    "fpurity": good.fpurity,
                    "fbuyprice": good.fbuyprice,
                    "fstorageflag": good.fstorageflag,
                    "fsafeqty": good.fsafeqty,
                    "fheightqty": good.fheightqty,
                    "fsafecard": good.fsafecard,
                    "fordunit": good.fordunit,
                    "fordpack": good.fordpack,
                    "fgoodstate": good.fgoodstate,
                    "userid": user_id,
                })
                # 返回
                d1 = {}
                d1['fsiteno'] = good.fsiteNo
                d1['fgoodno'] = good.fgoodno
                d1['fgoodname'] = good.fgoodname
                d1['fcasno'] = good.fcasno
                d1['fbarcode'] = good.fbarcode
                d1['fengname'] = good.fengname
                d1['fclassstno'] = good.fclassstno
                d1['fspec'] = good.fspec
                d1['funit'] = good.funit
                d1['feffday'] = good.feffday
                d1['fpurity'] = good.fpurity
                d1['fbuyprice'] = good.fbuyprice
                d1['fstorageflag'] = good.fstorageflag
                d1['fsafeqty'] = good.fsafeqty
                d1['fheightqty'] = good.fheightqty
                d1['fsafecard'] = good.fsafecard
                d1['fordunit'] = good.fordunit
                d1['fordpack'] = good.fordpack
                d1['fsupno'] = None
                d1['fgoodstate'] = good.fgoodstate
                d1['fupddate'] = None
                d1['foperator'] = user_id
                d1['fdanger'] = good.fdanger
                d1['fdensity'] = good.fdensity
                return BaseResponse.success(data=d1)
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@data_good_info_router.post("/update", summary="修改危化品物料信息")
def update_good_info(good: HazardousMaterialCreate, db: Session = Depends(get_db), token: str = Depends(oauth2_scheme)):
    try:
        userid = EncryptionMiddleware.get_current_user(token)
        with db.begin():
            sql = "update i_ts_good_info"
            sql = sql + " set fgoodname= :fgoodname ,fcasno= :fcasno "
            sql = sql + ",fbarcode= :fbarcode ,fengname= :fengname "
            sql = sql + ",fclassstno= :fclassstno ,fspec= :fspec "
            sql = sql + ",funit= :funit ,feffday= :feffday "
            sql = sql + ",fpurity= :fpurity ,fbuyprice= :fbuyprice "
            sql = sql + ",fstorageflag= :fstorageflag ,fsafeqty= :fsafeqty "
            sql = sql + ",fheightqty= :fheightqty ,fsafecard= :fsafecard "
            sql = sql + ",fordunit= :fordunit ,fordpack= :fordpack "
            sql = sql + ",fgoodstate= :fgoodstate ,fdanger= :fdanger ,fdensity= :fdensity "
            sql = sql + " ,fupddate=now(),foperator= :userid "
            sql = sql + " where fgoodno= :fgoodno  and fsite_no= :fsiteNo "
            db.execute(text(sql), {
                "fgoodname": good.fgoodname,
                "fcasno": good.fcasno,
                "fbarcode": good.fbarcode,
                "fengname": good.fengname,
                "fclassstno": good.fclassstno,
                "fspec": good.fspec,
                "funit": good.funit,
                "feffday": good.feffday,
                "fpurity": good.fpurity,
                "fbuyprice": good.fbuyprice,
                "fstorageflag": good.fstorageflag,
                "fsafeqty": good.fsafeqty,
                "fheightqty": good.fheightqty,
                "fsafecard": good.fsafecard,
                "fordunit": good.fordunit,
                "fordpack": good.fordpack,
                "fgoodstate": good.fgoodstate,
                "fdanger": good.fdanger,
                "fdensity": good.fdensity,
                "userid": userid,
                "fgoodno": good.fgoodno,
                "fsiteNo": good.fsiteNo,
            })
            return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


class GoodDelete(BaseModel):
    fgoodno: str = Field(..., description="物料编号")
    fsiteNo: str = Field(..., description="场地编号")


@data_good_info_router.post("/delete", summary="删除危化品物料信息")
def delete_good_info(good: GoodDelete, db: Session = Depends(get_db)):
    try:
        with db.begin():
            sql = "select sum(f1) "
            sql = sql + " from ( "
            sql = sql + " select count(1) f1 from i_tc_good_inv where fgoodno= :fgoodno "
            sql = sql + " union"
            sql = sql + " select count(1) f1 from i_tc_rec_detail where fgoodno= :fgoodno ) a"
            sql = sql + " where 1=1"

            data = db.execute(text(sql), {"fgoodno": good.fgoodno}).fetchall()

            if data[0][0] > 0:
                return BaseResponse.error(message="该资料在使用，不能删除")
            else:
                sql = "delete from i_ts_good_info where fgoodno= :fgoodno  and fsite_no= :fsiteNo "
                db.execute(text(sql), {
                    "fgoodno": good.fgoodno,
                    "fsiteNo": good.fsiteNo,
                })
                return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


class GoodSearch2(PaginationBase):
    fsiteNo: str = Field(None, description="场地编号")
    fpointNo: str = Field(None, description="编号")


@data_good_info_router.get("/list2", summary="查询危化品物料信息")
def list2_good_info(good: GoodSearch2, db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from i_warehouse_inv a,i_ts_good_info b,i_ts_warehouse c"
    sql = sql + " where 1=1"
    if good.fsiteNo is not None:
        sql = sql + " and a.fsite_no = :fsiteNo "
        param["fsiteNo"] = good.fsiteNo
    if good.fpointNo is not None:
        sql = sql + " and c.fpoint_no = :fpointNo "
        param["fpointNo"] = good.fpointNo
    sql = sql + " and a.fstate=1"
    sql = sql + " and a.fsite_no=b.fsite_no"
    sql = sql + " and a.fgoodno=b.fgoodno"
    sql = sql + " and a.fsite_no=c.fsite_no"
    sql = sql + " and a.fboxbarcode=c.fequipmentbarcode"
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select a.fboxbarcode,a.fboxbarcode,a.frfidbarcode,a.fsite_no,a.fqty"
    sql = sql + " ,b.fgoodname,b.fcasno,b.fbarcode,b.fclassstno,b.fbuyprice,b.fstorageflag "
    sql = sql + " ,b.feffday,b.fengname,b.fgoodstate,c.fequipmenttag,b.fgoodno"
    sql = sql + " from i_warehouse_inv a,i_ts_good_info b,i_ts_warehouse c"
    sql = sql + " where 1=1"
    if good.fsiteNo is not None:
        sql = sql + " and a.fsite_no = :fsiteNo "
        param["fsiteNo"] = good.fsiteNo
    if good.fpointNo is not None:
        sql = sql + " and c.fpoint_no = :fpointNo "
        param["fpointNo"] = good.fpointNo
    sql = sql + " and a.fstate=1"
    sql = sql + " and a.fsite_no=b.fsite_no"
    sql = sql + " and a.fgoodno=b.fgoodno"
    sql = sql + " and a.fsite_no=c.fsite_no"
    sql = sql + " and a.fboxbarcode=c.fequipmentbarcode"
    if good.order == "desc":
        sql = sql + " order by a.fboxbarcode,a.fboxbarcode,a.frfidbarcode desc "
    else:
        sql = sql + " order by a.fboxbarcode,a.fboxbarcode,a.frfidbarcode "
    sql = sql + " limit :limit offset :offset "
    param["limit"] = good.limit
    param["offset"] = good.offset
    data = db.execute(text(sql), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fboxbarcode'] = data[key][0]
        d1['fboxbarcode'] = data[key][1]
        d1['frfidbarcode'] = data[key][2]
        d1['fsiteNo'] = data[key][3]
        d1['fqty'] = data[key][4]
        d1['fgoodname'] = data[key][5]
        d1['fcasno'] = data[key][6]
        d1['fbarcode'] = data[key][7]
        d1['fclassstno'] = data[key][8]
        d1['fbuyprice'] = data[key][9]
        d1['fstorageflag'] = data[key][10]
        d1['feffday'] = float(data[key][11])
        d1['fengname'] = data[key][12]
        d1['fgoodstate'] = data[key][13]
        d1['fequipmenttag'] = data[key][14]
        d1['fgoodno'] = data[key][15]
        d1['fspec'] = None
        d1['funit'] = None
        d1['fupddate'] = None
        d1['fsafeqty'] = None
        d1['fsafecard'] = None
        d1['fpurity'] = None
        d1['fordunit'] = None
        d1['fordpack'] = None
        d1['foperator'] = None
        d1['fbalqty'] = None
        d1['fdanger'] = None
        d1['fdensity'] = None
        d1['fheightqty'] = None
        d1["details"] = None
        d1["details2"] = None
        d1["details4"] = None
        data_list.append(d1)

    info_dict = {"list": data_list,
                 "limit": good.limit,
                 "page": good.pageIndex,
                 "pages": (count + good.limit - 1) // good.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)
